const pool = require("./db.js");

// 获取所有未审核的取消请求
async function getUnauditedCancelRequests() {
  const [rows] = await pool.execute(
    `SELECT 
      cb.id AS cancelId,
      cb.order_id AS orderId,
      cb.cancel_time AS cancelTime,
      cb.refund_rate AS refundRate,
      cb.audit_status AS auditStatus,
      bo.id AS bookingId,
      bo.customer_id AS customerId,
      bo.room_id AS roomId,
      bo.start_time AS startTime,
      bo.end_time AS endTime,
      bo.participant_num AS participantNum,
      bo.order_status AS orderStatus,
      bo.payment_time AS paymentTime,
      mr.room_name AS roomName,
      mr.room_type AS roomType,
      u.real_name AS realName  -- 新增：从 user 表获取 real_name
     FROM cancel_booking cb
     JOIN booking_order bo ON cb.order_id = bo.id
     JOIN meeting_room mr ON bo.room_id = mr.id
     JOIN user u ON bo.customer_id = u.id  -- 新增：关联 user 表
     WHERE cb.audit_status = '待审核'`
  );
  return rows;
}

// 创建取消预订请求
async function createCancelRequest(cancelRequest) {
  const { orderId, cancelTime, refundRate, staffId, auditStatus } = cancelRequest;
  const [result] = await pool.execute(
    'INSERT INTO cancel_booking (order_id, cancel_time, refund_rate, staff_id, audit_status) VALUES (?, ?, ?, ?, ?)',
    [orderId, cancelTime, refundRate, staffId, auditStatus]
  );
  return result.insertId;
}

// 获取订单的取消记录
async function getCancelRequestsByOrderId(orderId) {
  const [rows] = await pool.execute(
    'SELECT * FROM cancel_booking WHERE order_id = ?',
    [orderId]
  );
  return rows;
}

// 更新取消请求状态
async function updateCancelRequestStatus(cancelId, auditStatus) {
  const [result] = await pool.execute(
    'UPDATE cancel_booking SET audit_status = ? WHERE id = ?',
    [auditStatus, cancelId]
  );
  return result.affectedRows > 0;
}

// 更新取消请求状态并记录审核人 staff_id
async function updateCancelRequestWithStaff(cancelId, auditStatus, staffId) {
  const [result] = await pool.execute(
    'UPDATE cancel_booking SET audit_status = ?, staff_id = ? WHERE id = ?',
    [auditStatus, staffId, cancelId]
  );
  return result.affectedRows > 0;
}

module.exports = {
  getUnauditedCancelRequests,
  createCancelRequest,
  getCancelRequestsByOrderId,
  updateCancelRequestStatus,
  updateCancelRequestWithStaff
};